/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.moedik.model;

import com.moedik.classes.ShuttleService;
import com.moedik.classes.ShuttleBook;
import com.moedik.classes.City;
import com.moedik.classes.Classes;
import com.moedik.classes.Kalender;
import com.moedik.classes.MailSender;
import com.moedik.classes.Pool;
import com.moedik.classes.Shuttle;
import com.moedik.classes.ShuttleServiceDb;
import com.moedik.classes.User;
import java.security.SecureRandom;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author SAMMY
 */
public class ShuttleModel extends Model {
    
    public ShuttleModel() {
        super();
    }

    public ArrayList<ShuttleService> getServices() {
        ArrayList<ShuttleService> services = new ArrayList<ShuttleService>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT `travel_services`.`id` AS `id`, `travel_services`.`travel_id` AS `travel_id`, `classes`.`name` AS `class_name`, `travels`.`capacity` AS `capacity`, `travels`.`total` AS `total`, `origin`.`city` AS `origin_city`, `origin`.`name` AS `origin_pool`, `origin`.`address` AS `origin_address`,`origin`.`phone` AS `origin_phone`,`destination`.`city` AS `destination_city`,`destination`.`name` AS `destination_pool`, `destination`.`address` AS `destination_address`, `destination`.`phone` AS `destination_phone`, `travel_services`.`fare` AS `fare`, `travel_services`.`day` AS `day`, `travel_services`.`hour` AS `hour` FROM `travel_services`JOIN(SELECT `travel_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `travel_services` JOIN `pools` JOIN `cities` WHERE `travel_services`.`origin_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `origin` JOIN (SELECT `travel_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `travel_services` JOIN `pools` JOIN `cities` WHERE `travel_services`.`destination_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `destination` JOIN `travels` JOIN `classes` WHERE `travel_services`.`travel_id` = `travels`.`id` AND `travels`.`class_id` = `classes`.`id` AND `origin`.`id` = `travel_services`.`id` AND `destination`.`id` = `travel_services`.`id` ORDER BY `origin`.`city`, `destination`.`city`, `travel_services`.`fare`";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                ShuttleService travelService = new ShuttleService(rs.getInt("id"), rs.getInt("travel_id"), rs.getString("class_name"), rs.getInt("capacity"), rs.getInt("total"), rs.getString("origin_city"), rs.getString("origin_pool"), rs.getString("origin_address"), rs.getString("origin_phone"), rs.getString("destination_city"), rs.getString("destination_pool"), rs.getString("destination_address"), rs.getString("destination_phone"), rs.getInt("fare"), rs.getString("day"), rs.getString("hour"));
                services.add(travelService);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return services;
    }

    public ArrayList<ShuttleService> getServices(int poolId, Kalender date) {
        ArrayList<ShuttleService> services = new ArrayList<ShuttleService>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            int day = date.getDayOfWeek();
            String query = "SELECT `travel_services`.`id` AS `id`, `travel_services`.`travel_id` AS `travel_id`, `classes`.`name` AS `class_name`, `travels`.`capacity` AS `capacity`, `travels`.`total` AS `total`, `origin`.`city` AS `origin_city`, `origin`.`name` AS `origin_pool`, `origin`.`address` AS `origin_address`,`origin`.`phone` AS `origin_phone`,`destination`.`city` AS `destination_city`,`destination`.`name` AS `destination_pool`, `destination`.`address` AS `destination_address`, `destination`.`phone` AS `destination_phone`, `travel_services`.`fare` AS `fare`, `travel_services`.`day` AS `day`, `travel_services`.`hour` AS `hour` FROM `travel_services`JOIN(SELECT `travel_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `travel_services` JOIN `pools` JOIN `cities` WHERE `travel_services`.`origin_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `origin` JOIN (SELECT `travel_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `travel_services` JOIN `pools` JOIN `cities` WHERE `travel_services`.`destination_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `destination` JOIN `travels` JOIN `classes` WHERE `travel_services`.`travel_id` = `travels`.`id` AND `travels`.`class_id` = `classes`.`id` AND `origin`.`id` = `travel_services`.`id` AND `destination`.`id` = `travel_services`.`id` AND `travel_services`.`origin_id` = '" + poolId + "' AND `travel_services`.`day` LIKE '%" + day + "%' ORDER BY `origin`.`city`, `destination`.`city`, `travel_services`.`fare`";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                ShuttleService service = new ShuttleService(rs.getInt("id"), rs.getInt("travel_id"), rs.getString("class_name"), rs.getInt("capacity"), rs.getInt("total"), rs.getString("origin_city"), rs.getString("origin_pool"), rs.getString("origin_address"), rs.getString("origin_phone"), rs.getString("destination_city"), rs.getString("destination_pool"), rs.getString("destination_address"), rs.getString("destination_phone"), rs.getInt("fare"), rs.getString("day"), rs.getString("hour"));
                services.add(service);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return services;
    }

    public ArrayList<ShuttleService> getServices(int poolId) {
        ArrayList<ShuttleService> services = new ArrayList<ShuttleService>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT `travel_services`.`id` AS `id`, `travel_services`.`travel_id` AS `travel_id`, `classes`.`name` AS `class_name`, `travels`.`capacity` AS `capacity`, `travels`.`total` AS `total`, `origin`.`city` AS `origin_city`, `origin`.`name` AS `origin_pool`, `origin`.`address` AS `origin_address`,`origin`.`phone` AS `origin_phone`,`destination`.`city` AS `destination_city`,`destination`.`name` AS `destination_pool`, `destination`.`address` AS `destination_address`, `destination`.`phone` AS `destination_phone`, `travel_services`.`fare` AS `fare`, `travel_services`.`day` AS `day`, `travel_services`.`hour` AS `hour` FROM `travel_services`JOIN(SELECT `travel_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `travel_services` JOIN `pools` JOIN `cities` WHERE `travel_services`.`origin_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `origin` JOIN (SELECT `travel_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `travel_services` JOIN `pools` JOIN `cities` WHERE `travel_services`.`destination_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `destination` JOIN `travels` JOIN `classes` WHERE `travel_services`.`travel_id` = `travels`.`id` AND `travels`.`class_id` = `classes`.`id` AND `origin`.`id` = `travel_services`.`id` AND `destination`.`id` = `travel_services`.`id` AND `travel_services`.`origin_id` = '" + poolId + "' ORDER BY `origin`.`city`, `destination`.`city`, `travel_services`.`fare`";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                ShuttleService service = new ShuttleService(rs.getInt("id"), rs.getInt("travel_id"), rs.getString("class_name"), rs.getInt("capacity"), rs.getInt("total"), rs.getString("origin_city"), rs.getString("origin_pool"), rs.getString("origin_address"), rs.getString("origin_phone"), rs.getString("destination_city"), rs.getString("destination_pool"), rs.getString("destination_address"), rs.getString("destination_phone"), rs.getInt("fare"), rs.getString("day"), rs.getString("hour"));
                services.add(service);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return services;
    }

    public ShuttleService getService(int id) {
        Statement stmt = null;
        ShuttleService service = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT `travel_services`.`id` AS `id`, `travel_services`.`travel_id` AS `travel_id`, `classes`.`name` AS `class_name`, `travels`.`capacity` AS `capacity`, `travels`.`total` AS `total`, `origin`.`city` AS `origin_city`, `origin`.`name` AS `origin_pool`, `origin`.`address` AS `origin_address`,`origin`.`phone` AS `origin_phone`,`destination`.`city` AS `destination_city`,`destination`.`name` AS `destination_pool`, `destination`.`address` AS `destination_address`, `destination`.`phone` AS `destination_phone`, `travel_services`.`fare` AS `fare`, `travel_services`.`day` AS `day`, `travel_services`.`hour` AS `hour` FROM `travel_services`JOIN(SELECT `travel_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `travel_services` JOIN `pools` JOIN `cities` WHERE `travel_services`.`origin_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `origin` JOIN (SELECT `travel_services`.`id` AS `id`,`pools`.`name` AS `name`,`pools`.`address` AS `address`,`pools`.`phone` AS `phone`,`cities`.`name` AS `city` FROM `travel_services` JOIN `pools` JOIN `cities` WHERE `travel_services`.`destination_id` = `pools`.`id` AND `pools`.`city_id` = `cities`.`id`) AS `destination` JOIN `travels` JOIN `classes` WHERE `travel_services`.`id` = '" + id + "' AND `travel_services`.`travel_id` = `travels`.`id` AND `travels`.`class_id` = `classes`.`id` AND `origin`.`id` = `travel_services`.`id` AND `destination`.`id` = `travel_services`.`id` ORDER BY `origin`.`city`, `destination`.`city`, `travel_services`.`fare`";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                service = new ShuttleService(rs.getInt("id"), rs.getInt("travel_id"), rs.getString("class_name"), rs.getInt("capacity"), rs.getInt("total"), rs.getString("origin_city"), rs.getString("origin_pool"), rs.getString("origin_address"), rs.getString("origin_phone"), rs.getString("destination_city"), rs.getString("destination_pool"), rs.getString("destination_address"), rs.getString("destination_phone"), rs.getInt("fare"), rs.getString("day"), rs.getString("hour"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return service;
    }

    public ArrayList<City> getCityServices() {
        ArrayList<City> cities = new ArrayList<City>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT DISTINCT origin.originCityId AS cityId, origin.originCity AS cityName FROM travel_services JOIN (SELECT pools.id As originId, cities.id as originCityId, cities.name as originCity FROM pools,cities WHERE pools.city_id=cities.id) As origin WHERE travel_services.origin_id = origin.originId";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                City city = new City(rs.getInt("cityId"), rs.getString("cityName"));
                cities.add(city);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return cities;
    }

    public ArrayList<Pool> getPoolServices(int cityId) {
        ArrayList<Pool> pools = new ArrayList<Pool>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT DISTINCT pools.id AS id, pools.name AS name, pools.address AS address, pools.phone AS phone FROM pools,travel_services WHERE pools.city_id=" + cityId + " AND travel_services.origin_id=pools.id";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Pool pool = new Pool(rs.getInt("id"), rs.getString("name"), rs.getString("address"), cityId, rs.getString("phone"));
                pools.add(pool);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return pools;
    }

    public ArrayList<Classes> getClasses() {
        ArrayList<Classes> c = new ArrayList<Classes>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM classes";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Classes cl = new Classes(rs.getInt(1), rs.getString(2));
                c.add(cl);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return c;
    }

    public ArrayList<Shuttle> getShuttle() {
        ArrayList<Shuttle> sh = new ArrayList<Shuttle>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM travels";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Shuttle s = new Shuttle();
                s.setId(rs.getInt(1));
                s.setName(rs.getString(2));
                sh.add(s);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return sh;
    }

    public String book(int user_id, int service_id, long date) {
        String bookingCode = null;

        String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        char[] buf = new char[6];
        SecureRandom r = new SecureRandom();

        for (int i = 0; i < buf.length; i++) {
            buf[i] = chars.charAt(r.nextInt(chars.length()));
        }

        String code = new String(buf);

        try {
            PreparedStatement stmt = null;
            ResultSet rs = null;

            String query = "INSERT INTO travel_books (id, user_id, shuttle_service_id, date, code) VALUES (NULL, '" + user_id + "', '" + service_id + "', '" + date + "', '" + code + "')";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
            rs = stmt.getGeneratedKeys();

            rs.next();

            bookingCode = code;
            LogModel lm = new LogModel();
            lm.bookShuttle(user_id, service_id);
        } catch (SQLException ex) {
            Logger.getLogger(ShuttleModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        String billno = null;

        if (bookingCode != null) {
            Date bookingDate = new Date(date);
            DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            String dateString = formatter.format(bookingDate);
            PaymentModel paymentModel = new PaymentModel();
            billno = paymentModel.register(user_id, 1, dateString, bookingCode);
            
            UserModel userModel = new UserModel();
            User user = userModel.get(user_id);

            ShuttleModel shuttleModel = new ShuttleModel();
            ShuttleService shuttleService = shuttleModel.getService(service_id);

            ConfigModel configModel = new ConfigModel();
            String companyName = configModel.getCompanyName();

            int fare = shuttleService.getFare();

            String[] recipient = {user.getEmail()};
            String body = "Yth. " + user.getName() + "\n\nTerima kasih atas pemesanan Anda.\n\nNomor pemesanan Anda adalah: " + billno + "\n\nTotal pembayaran Anda adalah: Rp. " + fare + ",-\n\nAnda dapat melakukan pembayaran melalui transfer ke rekening berikut:\n\nBank Mandiri Cabang Bandung Siliwangi\na.n. Samudra Harapan Bekti\nNo. Rek. 1300005229169\n\nSegera lakukan konfirmasi setelah Anda melakukan pembayaran agar dapat kami proses.\n\nTerima kasih.\n\n---\n" + companyName;
            MailSender.mail(recipient, "[" + companyName + "] Your Booking Information", body);
        }

        return billno;
    }

    public boolean available(int id, long date) {
        Statement stmt = null;
        boolean result = true;

        try {
            stmt = con.createStatement();
            String query = "SELECT `travels`.`capacity`, COUNT(`travel_books`.`shuttle_service_id`) AS `reserved_count` FROM `travel_books` JOIN `travel_services` JOIN `travels` WHERE `travel_books`.`shuttle_service_id` = `travel_services`.`id` AND `travel_services`.`travel_id` = `travels`.`id` AND `travel_books`.`shuttle_service_id` = '" + id + "' AND `travel_books`.`date` = '" + date + "' GROUP BY `travel_books`.`shuttle_service_id`";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                int capacity = rs.getInt(1);
                int reserved = rs.getInt(2);

                if (reserved >= capacity) {
                    result = false;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public void deleteService(int schedId) {
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "DELETE FROM travel_services WHERE id = " + schedId;
            stmt.executeUpdate(query);
            this.lm.log("layanan shuttle "+schedId+" telah dihapus");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void addService(int poolFrom, int poolTo, int shuttleId, String hour, int fare, String day) {
        PreparedStatement stmt = null;

        try {
            String query = "INSERT INTO travel_services (travel_id, origin_id, destination_id, fare, day, hour) VALUES (" + shuttleId + ", " + poolFrom + ", " + poolTo + "," + fare + ",'" + day + "','" + hour + "') ";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ShuttleServiceDb getServiceDb(int serviceId) {
        ShuttleServiceDb ss = new ShuttleServiceDb();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM travel_services WHERE id = " + serviceId;
            ResultSet rs = stmt.executeQuery(query);

            rs.next();

            ss.setId(rs.getInt(1));
            ss.setTravel_id(rs.getInt(2));
            ss.setOrigin_id(rs.getInt(3));
            ss.setDestination_id(rs.getInt(4));
            ss.setFare(rs.getInt(5));
            ss.setDay(rs.getString(6));
            ss.setHour(rs.getString(7));
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return ss;
    }

    public ArrayList<Pool> getPoolsFrom(int serviceId) {
        ArrayList<Pool> pools = new ArrayList<Pool>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT origin_id FROM travel_services WHERE id=" + serviceId;
            ResultSet rs = stmt.executeQuery(query);
            rs.next();

            int poolFrom = rs.getInt(1);

            stmt = con.createStatement();
            query = "SELECT city_id FROM pools WHERE id=" + poolFrom;
            rs = stmt.executeQuery(query);
            rs.next();

            int city = rs.getInt(1);

            stmt = con.createStatement();
            query = "SELECT id,name FROM pools WHERE city_id=" + city;
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                Pool p = new Pool();
                p.setId(rs.getInt(1));
                p.setName(rs.getString(2));
                pools.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return pools;
    }

    public ArrayList<Pool> getPoolsTo(int serviceId) {
        ArrayList<Pool> pools = new ArrayList<Pool>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT destination_id FROM travel_services WHERE id=" + serviceId;
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            int poolFrom = rs.getInt(1);

            stmt = con.createStatement();
            query = "SELECT city_id FROM pools WHERE id=" + poolFrom;
            rs = stmt.executeQuery(query);
            rs.next();
            int city = rs.getInt(1);

            stmt = con.createStatement();
            query = "SELECT id,name FROM pools WHERE city_id=" + city;
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                Pool p = new Pool();
                p.setId(rs.getInt(1));
                p.setName(rs.getString(2));
                pools.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return pools;
    }

    public void updateService(int schedId, int poolFrom, int poolTo, int shuttleId, String hour, int fare, String day) {
        PreparedStatement stmt = null;

        try {
            String query = "UPDATE travel_services SET travel_id=" + shuttleId + ", origin_id=" + poolFrom + ", destination_id=" + poolTo + ", fare=" + fare + ", day='" + day + "', hour='" + hour + "' WHERE id=" + schedId;
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
            this.lm.log("layanan shuttle "+schedId+" telah diperbaharui");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<ShuttleBook> getShuttleBooks() {
        ArrayList<ShuttleBook> shuttleBooks = new ArrayList<ShuttleBook>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM travel_books,travel_services WHERE shuttle_service_id=travel_services.id";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Statement stmt2 = con.createStatement();
                String query2 = "SELECT username FROM users WHERE id=" + rs.getInt("user_id");
                ResultSet rs2 = stmt2.executeQuery(query2);
                rs2.next();
                
                Statement stmt3 = con.createStatement();
                String query3 = "SELECT * FROM pools,cities WHERE city_id=cities.id AND pools.id=" + rs.getInt("origin_id");
                ResultSet rs3 = stmt3.executeQuery(query3);
                rs3.next();
                
                Statement stmt4 = con.createStatement();
                String query4 = "SELECT * FROM pools,cities WHERE city_id=cities.id AND pools.id=" + rs.getInt("destination_id");
                ResultSet rs4 = stmt4.executeQuery(query3);
                rs4.next();

                int id = rs.getInt("id");
                String username = rs2.getString("username");
                String originCity = rs3.getString("cities.name");
                String originPool = rs3.getString("pools.name");
                String destinationCity = rs4.getString("cities.name");
                String destinationPool = rs4.getString("pools.name");
                String shuttleClass = "";
                String date = rs.getString("date");
                String code = rs.getString("code");

                ShuttleBook shuttleBook = new ShuttleBook(id, username, originCity, originPool, destinationCity, destinationPool, shuttleClass, date, code);
                shuttleBooks.add(shuttleBook);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return shuttleBooks;
    }

    public void deleteShuttleBook(int shuttleBookId) {
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "DELETE FROM travel_books WHERE id = '" + shuttleBookId + "'";
            stmt.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public Pool getPool(int poolId){
        Statement stmt = null;
        Pool p = null;
        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM pools WHERE id=" + poolId;
            ResultSet rs = stmt.executeQuery(query);

            rs.next();
            p = new Pool();
            p.setId(rs.getInt(1));
            p.setName(rs.getString(2));
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return p;
    }

    public ArrayList<Integer> getReport() {
        ArrayList<Integer> result = new ArrayList<Integer>();
        Statement stmt = null;
        int count;
        try {
            Calendar cal = Calendar.getInstance();
            String first,last;
            for(int i=0;i<12;i++){
                cal.set(Calendar.MONTH, i);
                cal.set(Calendar.DATE, 1);
                first = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-01";
                cal.add(Calendar.MONTH, 1);
                cal.add(Calendar.DATE, -1);
                last = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
                
                stmt = con.createStatement();
                String query = "SELECT COUNT(id) FROM payments WHERE date between '"+first+"' AND '"+last+"' AND service=1";
                ResultSet rs = stmt.executeQuery(query);
                rs.next();
                count = rs.getInt(1);
                result.add(count);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public ArrayList<Integer> getReport(int year) {
        ArrayList<Integer> result = new ArrayList<Integer>();
        Statement stmt = null;
        int count;
        try {
            Calendar cal = Calendar.getInstance();
            cal.set(Calendar.YEAR, year);
            String first,last;
            for(int i=0;i<12;i++){
                cal.set(Calendar.MONTH, i);
                cal.set(Calendar.DATE, 1);
                first = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-01";
                cal.add(Calendar.MONTH, 1);
                cal.add(Calendar.DATE, -1);
                last = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
                
                stmt = con.createStatement();
                String query = "SELECT COUNT(id) FROM payments WHERE date between '"+first+"' AND '"+last+"' AND service=1";
                ResultSet rs = stmt.executeQuery(query);
                rs.next();
                count = rs.getInt(1);
                result.add(count);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
